package ru.hh.team02.chat.database;

import java.sql.*;


public class DB
{
    private static String dbURL = "jdbc:derby:hh0001team02;user=team02;create=false";
    private static Connection conn = null;
    private static Statement stmt = null;

    public static void main(String[] args) {
        DB db = new DB();
        db.createConnection();
        db.getUserById(1);
        db.getMessageById(2);
        db.getRoomById(3);
        db.getMessagesByUserName("123");
        db.getRoomNameByUserName("123");
        int i = db.insertUser("petr", "pass");
        i = db.insertRoom("room212");
        i = db.insertMessage("blabla","petr");
        db.deleteMessageById(i);
        db.shutdown();
    }

    private void createConnection() {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();
            //Get a connection
            conn = DriverManager.getConnection(dbURL);
        }
        catch (Exception except)
        {
            except.printStackTrace();
        }
    }

    public String[] getMessageById(int id) {
        String [] result = null;
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("select rctpto_id, message from messages where id = " + id);

            if (resultSet.next())
            {
                result = new String[2];
                result[0] =  resultSet.getString("rctpto_id");
                result[1] = resultSet.getString("message");
            }
            resultSet.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public String[] getUserById(int id) {
        String [] result = null;
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("select name, room_id from users where id = " + id);

            if (resultSet.next())
            {
                result = new String[2];
                result[0] =  resultSet.getString("name");
                result[1] = resultSet.getString("room_id");
            }
            resultSet.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public String getRoomById(int id) {
        String result = null;
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("select name from rooms where id = " + id);

            if (resultSet.next())
            {
                result =  resultSet.getString("name");
            }
            resultSet.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public String[] getMessagesByUserName(String userName) {
        String [] result = null;
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("select messages.message from messages join users on messages.rctpto_id = users.id where users.name = '" + userName + "'");
            result = new String[1000];          //TODO: откуда брать размер
            //TODO [Peter] Используй тут LinkedList. Нам все равно Ransom access не нужен.

            int i = 0;
            while (resultSet.next())
            {
                result[i] = resultSet.getString("message");
                i++;
            }
            resultSet.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public String getRoomNameByUserName(String userName) {
        String result = null;
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("select rooms.name from users join rooms on users.room_id = rooms.id where users.name = '" + userName + "'");
            if (resultSet.next())
            {
                result = resultSet.getString("name");
            }
            resultSet.close();
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public int insertUser(String name, String pass) {
        int result = 0;
        try
        {
            stmt = conn.createStatement();
            result = stmt.executeUpdate("insert into users(name, password) values ('" + name +  "', '" + pass + "')",
                    Statement.RETURN_GENERATED_KEYS);
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public int insertRoom(String name) {
        int result = 0;
        try
        {
            stmt = conn.createStatement();
            result = stmt.executeUpdate("insert into rooms(name) values ('" + name + "')",
                    Statement.RETURN_GENERATED_KEYS);
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public int insertMessage(String message, String rctptoName) {
        int result = 0;
        try
        {
            stmt = conn.createStatement();
            ResultSet resultSet = stmt.executeQuery("select id from users where name = " + rctptoName);
            int rctpto_id = resultSet.getInt("id");
            resultSet.close();
            result = stmt.executeUpdate("insert into messages(message, rctpto_id) values ('" + message + ", " + rctpto_id + "')", Statement.RETURN_GENERATED_KEYS);
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }
        return result;
    }

    public void deleteMessageById(int id) {
        try
        {
            stmt = conn.createStatement();
            stmt.execute("delete from messages where id = " + id);
            stmt.close();
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }

    }

    private void shutdown() {
        try
        {
            if (stmt != null)
            {
                stmt.close();
            }
            if (conn != null)
            {
                DriverManager.getConnection(dbURL + ";shutdown=true");
                conn.close();
            }
        }
        catch (SQLException sqlExcept)
        {
            sqlExcept.printStackTrace();
        }

    }
}